/* This file aims to create the file after merging HIMSS and AHA, with adoption info and hospital characteristics   */
clear all
set more off
cd "" //targed directory


clear all
set more off
forvalues i=2006/2010 {
clear all
quietly odbc list
quietly odbc query "`i'"
quietly odbc desc "HAEntity"
odbc load, table("HAEntity")
rename MedicareNumber mcrnum 
destring mcrnum,replace force
keep HAEntityId UniqueId mcrnum NofBeds
tempfile haeid
save `haeid'


********** Matched ID for both HIMSS and AHA
clear
use "./prepare/id_matched.dta",clear
*use "./prepare/id_matched.dta",clear
tempfile id
save `id'


********** Hospital characteristics
clear

use "./rawdata/aha_extract`i'.dta",clear
gen year=`i'
keep id mcrnum mhsmemb sysid cntrl serv bdtot admtot ipdtot mcrdc mcripd mcddc mcdipd births vtot ftmdtf mapp8 mloczip fcounty year

gen teach=0    
destring mapp8,replace force
replace teach=1 if mapp8==1
destring cntrl,replace force   
gen nprofit=0
replace nprofit=1 if cntrl==21 | cntrl==22 | cntrl==23
gen profit=0
replace profit=1 if cntrl==30 | cntrl==31 | cntrl==32 | cntrl==33
gen perc_mcd=mcddc/admtot    
gen perc_mcr=mcrdc/admtot 

tempfile aha`i'
save `aha`i''



********** Adoption data (HIMSS)
clear
quietly odbc list
quietly odbc query "`i'"
quietly odbc desc "HAEntityApplication"
odbc load, table("HAEntityApplication")
merge m:1 HAEntityId using `haeid'
keep if _merge==3
drop _merge
keep UniqueId HAEntityId NofBeds ApplicationId CategoryId Status SoftwareVendorId SoftwareProductId ContractYear ImplementedYear mcrnum NofBeds
keep if CategoryId==6  
keep if ApplicationId==31  


****** Drop observations that are not the actual choice of the hospital
duplicates drop UniqueId SoftwareVendorId,force
duplicates tag UniqueId,gen (dup_uid)
drop if dup_uid>0 & Status=="To be Replaced"
drop dup_uid
duplicates tag UniqueId,gen (dup_uid)
drop if dup_uid>0 & Status=="Not Automated"
drop dup_uid
duplicates tag UniqueId,gen (dup_uid)
drop if dup_uid>0 & Status=="Not Yet Contracted"
drop dup_uid
duplicates tag UniqueId,gen (dup_uid)
bys UniqueId dup_uid:egen impyr=max(ContractYear)
drop if dup_uid>0 & impyr~=ContractYear
drop dup_uid impyr
duplicates tag UniqueId,gen (dupid)


*** The rest obs with dupid>0 are replicate
sort UniqueId SoftwareVendorId
duplicates drop UniqueId,force


****** Re-ID vendor identity
gen adp=(Status=="Live and Operational")
gen vendor=SoftwareVendorId*adp
drop adp
gen adj_vnd=0
replace adj_vnd=1 if vendor==0
replace adj_vnd=2 if vendor==2577
replace adj_vnd=3 if vendor==470
replace adj_vnd=4 if vendor==668
replace adj_vnd=5 if vendor==729
replace adj_vnd=6 if vendor==890
replace adj_vnd=7 if vendor==936
replace adj_vnd=8 if vendor==1062
replace adj_vnd=9 if vendor==1200
replace adj_vnd=10 if vendor==1704
replace adj_vnd=11 if vendor==2613
replace adj_vnd=12 if vendor==5153
replace adj_vnd=13 if vendor~=0 & vendor~=2577 & vendor~=470 & vendor~=668 & vendor~=729 & vendor~=890 & vendor~=936 & vendor~=1062 & vendor~=1200 & vendor~=1704 & vendor~=2613 & vendor~=5153

di "YEAR: `i'"

merge 1:m UniqueId using `id'
keep if _merge==3
drop _merge


merge m:1 id using `aha`i'',force
keep if _merge==3
drop _merge

save "./prepare/basic`i'",replace
 }

 
 
 
 
 
******************************
********** Market info
clear
insheet using "./rawdata/hsa.csv",clear
tempfile hsa
save `hsa'

********** Combine all years and create state varibles---existing choice---and action variable---choice for the next period
clear
foreach i of num 2006/2009 {
use "./prepare/basic`i'",clear
keep id year adj_vnd
rename adj_vnd prechs
tempfile preadp`i'
save `preadp`i''
}



clear
foreach i of num 2007/2010 {
use "./prepare/basic`i'",clear
replace year=year-1
local yr=`i'-1
merge 1:1 id year using `preadp`yr''
drop _merge
tempfile base`i'
save `base`i''
}



clear
use `base2007',clear
append using `base2008',force
append using `base2009',force
append using `base2010',force


****** Create market ID
destring fcounty,gen(fips) force
merge m:1 fips using `hsa'
drop if _merge==2
drop _merge


bys id:egen hsatmp=mode(hsa)
replace hsa=hsatmp if hsa==.
drop hsatmp



****** Distinguish stand-alone and affiliated hospitals
destring sysid,force replace
replace sysid=0 if sysid==.



****** create numerical format of id: ahaid
destring id,gen(ahaid) force
replace ahaid=6360604 if id=="636060A"
replace ahaid=6361184 if id=="636118A"
replace ahaid=6380804 if id=="638080A"
replace ahaid=6390764 if id=="639076A"
replace ahaid=6530327 if id=="653032B"
replace ahaid=6530904 if id=="653090A"
replace ahaid=6530954 if id=="653095A"
replace ahaid=6540904 if id=="654090A"
replace ahaid=6630154 if id=="663015A"
replace ahaid=6650431 if id=="665043A"
replace ahaid=6710114 if id=="671011A"
replace ahaid=6730504 if id=="673050A"
replace ahaid=6931394 if id=="693139A"


save "./prepare/allbasic",replace





















